A spreadsheet application is a computer program that allows a user to manipulate two-dimensional and three-dimensional arrays of data. A user of a spreadsheet application is presented with a two-dimensional array of cells, each cell being capable of storing an entity such as an item of numeric data, text, a software object, or a formula. A three-dimensional spreadsheet has several of such arrays having a definite order. Where a cell contains a formula, the display normally shows the result of the calculation that the formula defines. The formula can include amongst its input variables the value of another cell within the spreadsheet or within another spreadsheet. Of course, the value of the other cell may be the result of calculation of a formula within the other cell. Such linkage between cells can be extended to an arbitrary degree of complexity.
Spreadsheets were conceived as tools to be used for performing quick calculations on personal data. In contrast, data and functionality that are critical to the operations of an organisation would traditionally have been stored in databases supporting computer applications that have been developed by programmers to perform specific, controlled actions on the data. Computer spreadsheets have become ubiquitous in many organisations as a result of the growth in power of personal computers. Therefore, in practice, this traditional model of how data is managed is no longer followed in many organisations. Spreadsheets that were hurriedly put together to perform a calculation that the programmed database lacked have become essential tools central to the work of their creators, and, effectively, central to the operation of the business.
In the present situation, the features of spreadsheets that make them very attractive to their users become unwelcome from the point of view of the organisation as a whole. A spreadsheet can be changed very easily. However, the interconnected nature of the cells in a spreadsheet can mean that the consequences of such a change can be very difficult to foresee. This is especially so in cases where the spreadsheet is referenced by another spreadsheet, possibly developed by another person. It is also not possible to determine the content of a spreadsheet at any given moment, so that the reasons behind a conclusion being drawn from the content of a spreadsheet may not be traceable. Moreover, the power of modern personal computers is such as to make practicable implementation of a scripting language in spreadsheet applications to perform calculations and actions of arbitrary complexity. Yet this code is not subject to version control and change tracking familiar to programmers in traditional programming languages.
Clearly, this situation is unacceptable in a large organisation since it renders the quality control of data and functionality impossible, and provides an obstacle to creation of auditable business activity (such as preparation of accounts) within the business that are required as a deterrent to financial mismanagement.
In commercial applications, often many spreadsheets are required and the size of each spreadsheet can become large. This results in a significant amount of memory being required to store all of the spreadsheets. Typically, 70% of cells in a spreadsheet contain formulas. Formulas are usually stored as character strings whose size often exceeds the number of characters in the resulting value derived from the formula. For these reasons, the space used to store formulas within a spreadsheet can represent a significant proportion of the overall space used. For example, up to 90% of the space used in a spreadsheet may be used to store formulas. Furthermore, typically, a very high proportion of the formulas in a spreadsheet are duplicated. It is a common scenario for example for formula use to create a formula at the top of a column and then drag it down applying the formula to the cells underneath. In some cases, up to 99.5% of formulas within a spreadsheet are duplicated.
An aim of this invention is to provide a mechanism whereby actions performed upon a spreadsheet can be recorded in such a manner that the state of the spreadsheet at any particular time can be recovered and the evolution between states can be interrogated.
A further aim of the invention is to determine changes in a spreadsheet in such a way that actions such as row and column insertions and deletions and sorting actions which cause significant numbers of entities in the spreadsheet to shift are taken into account.
A further aim of this invention is to provide a mechanism whereby entities such as formulas occurring within a spreadsheet may be stored in a more efficient manner.
It is a further aim of the invention that the inherent flexibility of the spreadsheet, which makes it so attractive, is not lost.